USE [SI2Final]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'[dbo].[spRelatorioConsultas]', N'P') IS NOT NULL
    DROP procedure [dbo].spRelatorioConsultas;
GO
CREATE PROCEDURE spRelatorioConsultas
	@medico int,
	@dataInit date,
	@dataFim date,
	@nrConsultas int output,
	@montante int output
AS
BEGIN
	SET NOCOUNT ON;
	set xact_abort on;
		
BEGIN TRAN	

	select @nrConsultas=ISNULL(COUNT(c.IdConsulta),0), @montante=ISNULL(SUM(i.Montante),0) from Pessoa p inner join Medico m on p.NumeroSS=m.IdPessoa 
		inner join Consulta c on c.IdMedico=m.LicencaMedica 
		left join Relatorio r on r.IdConsulta=c.IdConsulta
		left join ItemFatura i on i.IdRelatorio=r.IdRelatorio
		where p.NumeroSS=@medico and (c.Data between @dataInit and @dataFim)
		group by p.NumeroSS
	
	
COMMIT TRANSACTION

END
GO

